--DROP VIEW V_APP_AUDIT;

/* Formatted on 2013/11/19 18:14:49 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_APP_AUDIT
(
   ID,
   PROID,
   AUDITTYPE,
   AUDITBY,
   AUDITTIME,
   AUDITRESULT,
   AUDITCONTENT,
   APPLYNO,
   CUSTNAME,
   CUSTNO
)
AS
   SELECT DISTINCT aa."ID",
                   aa."PROID",
                   aa."AUDITTYPE",
                   aa."AUDITBY",
                   aa."AUDITTIME",
                   aa."AUDITRESULT",
                   aa."AUDITCONTENT",
                   ap.applyno,
                   ap.custname,
                   ap.custno
     FROM TB_APP_AUDIT aa LEFT JOIN tb_app_proinfo ap ON aa.proid = ap.id;


--DROP VIEW V_APP_REFREGINFO;

/* Formatted on 2013/11/19 18:14:49 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_APP_REFREGINFO
(
   ID,
   REGINFOID,
   CUSTNAME,
   REFBY,
   REFBYNAME,
   APPDATE,
   OUTDEPID,
   OUTDEPNAME,
   OUTTIME,
   INDEPID,
   INDEPNAME,
   INTIME,
   SALESMANID,
   SALESMAN,
   DETAIL,
   STATUS,
   AUDITBY,
   AUDITTIME,
   BACKSTATUS,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   CUSTNO
)
AS
   SELECT DISTINCT r."ID",
                   r."REGINFOID",
                   r."CUSTNAME",
                   r."REFBY",
                   r."REFBYNAME",
                   r."APPDATE",
                   r."OUTDEPID",
                   r."OUTDEPNAME",
                   r."OUTTIME",
                   r."INDEPID",
                   r."INDEPNAME",
                   r."INTIME",
                   r."SALESMANID",
                   r."SALESMAN",
                   r."DETAIL",
                   r."STATUS",
                   r."AUDITBY",
                   r."AUDITTIME",
                   r."BACKSTATUS",
                   r."SPAREFIELD1",
                   r."SPAREFIELD2",
                   r."SPAREFIELD3",
                   ri.custno
     FROM TB_APP_REFREGINFO r
          LEFT JOIN tb_app_reginfo ri ON r.REGINFOID = ri.id;


--DROP VIEW V_APP_SPECIALINFO_ADU;

/* Formatted on 2013/11/19 18:14:49 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_APP_SPECIALINFO_ADU
(
   ID,
   LOANID,
   PROID,
   CONID,
   PID,
   SPECIALTYPE,
   SUBMITDEPART,
   SUBMITUSER,
   AUDITTIME,
   AUDITSTATUS,
   REMARK,
   TODEPART,
   TOUSER,
   REGINFOID,
   CUSTNAME,
   SALESMANID,
   SALESMAN,
   PRODUCTID,
   PRODUCTNAME,
   CREATEDATE
)
AS
   SELECT DISTINCT s."ID",
                   s."LOANID",
                   s."PROID",
                   s."CONID",
                   s."PID",
                   s."SPECIALTYPE",
                   s."SUBMITDEPART",
                   s."SUBMITUSER",
                   s."AUDITTIME",
                   s."AUDITSTATUS",
                   s."REMARK",
                   s."TODEPART",
                   s."TOUSER",
                   asp.reginfoid,
                   asp.custname,
                   asp.SALESMANID,
                   asp.SALESMAN,
                   asp.PRODUCTID,
                   asp.PRODUCTNAME,
                   asp.CREATEDATE
     FROM TB_ADU_SPECAIL s
          LEFT JOIN tb_app_specialinfo asp
             ON s.SPECIALTYPE = '5' AND s.pid = asp.id;


--DROP VIEW V_PRODUCT_METINFOSETDETAIL;

/* Formatted on 2013/11/19 18:14:49 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_PRODUCT_METINFOSETDETAIL
(
   INFONAME,
   METINFOSETDETAILID,
   SORTNO,
   PRODUCTID
)
AS
   SELECT d.INFONAME,
          d.id metinfosetdetailid,
          d.sortno,
          p.id productid
     FROM tb_sys_metinfosetmain m,
          tb_sys_metinfosetdetail d,
          tb_sys_product p
    WHERE m.id = d.METIINFOID AND m.id = p.METINFOSETID;
